package com.hrm.dao.Impl;

import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;

import com.hrm.dao.EmployeeDao;
import com.hrm.domain.EmpExt;
import com.hrm.domain.Employee;
import com.hrm.utils.JDBCTools;

/*
 * 员工数据处理层实现类
 */
@SuppressWarnings("all") // 使用注解抑制警告信息
public class EmployeeDaoImpl implements EmployeeDao {

	// 定义全局变量
	private Class eClass = Employee.class;
	private Class eeClass = EmpExt.class;

	// 根据部门ID查找员工
	@Override
	public List<Employee> getEmpsByDeptID(int deptID) {
		String sql = "select `ID` empID, `DEPT_ID` empDID, `JOB_ID` empJID, `NAME` empName, `CARD_ID` empCardID, `ADDRESS` empAddress, `POST_CODE` empPostCode, `TEL` empTel, `PHONE` empPhone, `QQ_NUM` empQQ, `EMAIL` empEmail, `SEX` empSex, `PARTY` empParty, `BIRTHDAY` empBirth, `RACE` empRace, `EDUCATION` empEdu, `SPECIALITY` empSpec, `HOBBY` empHobby, `REMARK` empRemark, `CREATE_DATE` empCreateDate from employee_inf where `DEPT_ID`=?;";
		return JDBCTools.dataQuery(eClass, sql, deptID);
	}

	// 根据员工ID修改员工的部门
	@Override
	public Integer modifyEmpDeptByID(int deptID, int ID) {
		String sql = "UPDATE `employee_inf` SET `DEPT_ID` = ? WHERE `ID` = ?;";
		return JDBCTools.dataUpdate(sql, deptID, ID);
	}

	// 根据职位ID查找员工
	@Override
	public List<Employee> getEmpsByJobID(int jobID) {
		String sql = "select `ID` empID, `DEPT_ID` empDID, `JOB_ID` empJID, `NAME` empName, `CARD_ID` empCardID, `ADDRESS` empAddress, `POST_CODE` empPostCode, `TEL` empTel, `PHONE` empPhone, `QQ_NUM` empQQ, `EMAIL` empEmail, `SEX` empSex, `PARTY` empParty, `BIRTHDAY` empBirth, `RACE` empRace, `EDUCATION` empEdu, `SPECIALITY` empSpec, `HOBBY` empHobby, `REMARK` empRemark, `CREATE_DATE` empCreateDate from employee_inf where `JOB_ID`=?;";
		return JDBCTools.dataQuery(eClass, sql, jobID);
	}

	// 根据员工ID修改员工的职位
	@Override
	public Integer modifyEmpJobByID(int jobID, int ID) {
		String sql = "UPDATE `employee_inf` SET `JOB_ID` = ? WHERE `ID` = ?;";
		return JDBCTools.dataUpdate(sql, jobID, ID);
	}

	// 获取查询到的记录条数
	@Override
	public Integer getCount(HashSet<Integer> flag, Object... parametes) {
		StringBuffer sql = new StringBuffer("select count(*) from employee_inf");
		if (!flag.contains(1)) {
			sql.append(" e  join dept_inf d on e.DEPT_ID = d.ID join job_inf j on e.JOB_ID = j.ID where e.`NAME` like "
					+ parametes[0] + " and e.`CARD_ID` like " + parametes[1] + " and e.`PHONE` like " + parametes[2]);
		}
		if (flag.contains(2)) { // 有性别
			sql.append(" and e.SEX = " + parametes[3]);
		}
		if (flag.contains(3)) { // 有职位
			sql.append(" and e.JOB_ID = " + parametes[4]);
		}
		if (flag.contains(4)) { // 有部门
			sql.append(" and e.DEPT_ID = " + parametes[5]);
		}
		sql.append(";");
		return JDBCTools.dataCount(sql.toString());
	}

	// 分页 + 模糊
	@Override
	public List<EmpExt> getEmpExtsByTerm(HashSet<Integer> flag, Object... parametes) {
		StringBuffer sql = new StringBuffer(
				"select e.ID empID,e.`NAME` empName,e.CARD_ID empCardID,e.ADDRESS empAddress,e.PHONE empPhone,e.SEX empSex,e.EDUCATION empEdu,e.SPECIALITY empSpec,e.CREATE_DATE empCreateDate,d.`NAME` deptName,j.`NAME` jobName from employee_inf e join dept_inf d on e.DEPT_ID = d.ID join job_inf j on e.JOB_ID = j.ID");
		if (!flag.contains(1)) { // 有查询条件
			sql.append(" where e.`NAME` like " + parametes[0] + " and e.`CARD_ID` like " + parametes[1]
					+ " and e.`PHONE` like " + parametes[2]);
		}
		if (flag.contains(2)) { // 有性别
			sql.append(" and e.SEX = " + parametes[3]);
		}
		if (flag.contains(3)) { // 有职位
			sql.append(" and e.JOB_ID = " + parametes[4]);
		}
		if (flag.contains(4)) { // 有部门
			sql.append(" and e.DEPT_ID = " + parametes[5]);
		}
		if (flag.contains(1)) { // 没有查询条件
			sql.append(" limit " + parametes[0] + "," + parametes[1] + ";");
		} else { // 有查询条件
			sql.append(" limit " + parametes[6] + "," + parametes[7] + ";");
		}
		return JDBCTools.dataQuery(eeClass, sql.toString());
	}

	// 添加员工
	@Override
	public Integer addEmp(Employee emp) {
		String sql = "INSERT INTO `employee_inf` ( `DEPT_ID`, `JOB_ID`, `NAME`, `CARD_ID`, `ADDRESS`, `POST_CODE`, `TEL`, `PHONE`, `QQ_NUM`, `EMAIL`, `SEX`, `PARTY`, `BIRTHDAY`, `RACE`, `EDUCATION`, `SPECIALITY`, `HOBBY`, `REMARK`) VALUES ( ?, ?, ?, ?, ?, '510000', '020-77777777', ?, '36750066', '251425887@qq.com', ?, '群众', '1980-01-01 00:00:00', '汉', ?, ?, '唱跳rap', '无');";
		return JDBCTools.dataUpdate(sql, emp.getEmpDID(), emp.getEmpJID(), emp.getEmpName(), emp.getEmpCardID(),
				emp.getEmpAddress(), emp.getEmpPhone(), emp.getEmpSex(), emp.getEmpEdu(), emp.getEmpSpec());
	}

	// 根据员工姓名获取指定员工的信息
	@Override
	public Employee getEmpByEN(String empName) {
		Employee emp = null;
		String sql = "select `ID` empID, `DEPT_ID` empDID, `JOB_ID` empJID, `NAME` empName, `CARD_ID` empCardID, `ADDRESS` empAddress, `POST_CODE` empPostCode, `TEL` empTel, `PHONE` empPhone, `QQ_NUM` empQQ, `EMAIL` empEmail, `SEX` empSex, `PARTY` empParty, `BIRTHDAY` empBirth, `RACE` empRace, `EDUCATION` empEdu, `SPECIALITY` empSpec, `HOBBY` empHobby, `REMARK` empRemark, `CREATE_DATE` empCreateDate from employee_inf where `NAME`=?;";
		List<Employee> emps = JDBCTools.dataQuery(eClass, sql, empName);
		if (!emps.isEmpty()) {
			emp = emps.get(0);
		}
		return emp;
	}

	// 根据员工ID获取指定员工的信息
	@Override
	public Employee getEmpByID(int ID) {
		Employee emp = null;
		String sql = "select `ID` empID, `DEPT_ID` empDID, `JOB_ID` empJID, `NAME` empName, `CARD_ID` empCardID, `ADDRESS` empAddress, `POST_CODE` empPostCode, `TEL` empTel, `PHONE` empPhone, `QQ_NUM` empQQ, `EMAIL` empEmail, `SEX` empSex, `PARTY` empParty, `BIRTHDAY` empBirth, `RACE` empRace, `EDUCATION` empEdu, `SPECIALITY` empSpec, `HOBBY` empHobby, `REMARK` empRemark, `CREATE_DATE` empCreateDate from employee_inf where `ID`=?;";
		List<Employee> emps = JDBCTools.dataQuery(eClass, sql, ID);
		if (!emps.isEmpty()) {
			emp = emps.get(0);
		}
		return emp;
	}

	// 根据身份证查找指定员工的信息
	@Override
	public Employee getEmpByCard(String card) {
		Employee emp = null;
		String sql = "select `ID` empID, `DEPT_ID` empDID, `JOB_ID` empJID, `NAME` empName, `CARD_ID` empCardID, `ADDRESS` empAddress, `POST_CODE` empPostCode, `TEL` empTel, `PHONE` empPhone, `QQ_NUM` empQQ, `EMAIL` empEmail, `SEX` empSex, `PARTY` empParty, `BIRTHDAY` empBirth, `RACE` empRace, `EDUCATION` empEdu, `SPECIALITY` empSpec, `HOBBY` empHobby, `REMARK` empRemark, `CREATE_DATE` empCreateDate from employee_inf where `CARD_ID`=?;";
		List<Employee> emps = JDBCTools.dataQuery(eClass, sql, card);
		if (!emps.isEmpty()) {
			emp = emps.get(0);
		}
		return emp;
	}

	// 根据员工ID删除指定员工的信息
	@Override
	public Integer delEmp(int ID) {
		String sql = "delete from `employee_inf` where `ID` = ?;";
		return JDBCTools.dataUpdate(sql, ID);
	}

	// 根据员工ID修改指定员工的信息
	@Override
	public Integer modifyEmpByID(Employee newEmp, int ID) {
		String sql = "UPDATE `employee_inf` SET  `DEPT_ID` = ? ,`JOB_ID` = ?, `NAME` = ?, `CARD_ID` = ?, `ADDRESS` = ?, `PHONE` = ?, `SEX` = ?, `EDUCATION` = ?, `SPECIALITY` = ? WHERE `ID` = ?;";
		return JDBCTools.dataUpdate(sql, newEmp.getEmpDID(), newEmp.getEmpJID(), newEmp.getEmpName(),
				newEmp.getEmpCardID(), newEmp.getEmpAddress(), newEmp.getEmpPhone(), newEmp.getEmpSex(),
				newEmp.getEmpEdu(), newEmp.getEmpSpec(), ID);
	}

}
